*****************************************************************************************

* This file imports and cleans LIHTC data provided by HUD

*****************************************************************************************

/* steps in below file

1. import and clean file
2. rectangularize file so that missing town-years appear in data with zero buildings

*/


** set directory

* import
import excel "lihtc.xlsx", sheet("LIHTCPUB") firstrow clear
drop contact company co_add co_cty co_st co_zip co_tel reg msa cbsa cosubcur fips1990 st1990 cnty1990 trct1990 fips2000 st2000 cnty2000 trct2000 fips2010 st2010 cnty2010 trct2010 resynd allocamt inc_ceil low_ceil ceilunit dda qct nonprog datanote record_status x y z basis bond mff_ra fmha_514 fmha_515 fmha_538 home home_amt tcap tcap_amt cdbg cdbg_amt fha hopevi hpvi_amt tcep tcep_amt rentassist trgt_pop trgt_fam trgt_eld trgt_dis trgt_hml trgt_other trgt_spc type credit metro yrmisflg 

gen placfips = placefp
replace placfips = place2010 if place2010 != .
rename placfips placefips
drop if placefips == .
gen n_proj = 1

* get state - state fips cross walk
merge 1:1 hud_id project using "statastates_workaround.dta"
drop if _merge == 1
drop _merge
drop if yr_alloc > 2019
drop if placefips == 99999

* collapse to place level
collapse (sum) n_proj n_units li_units n_0br n_2br n_1br n_3br n_4br, by(state_fips placefips yr_alloc)
rename state_fips statefips


*force rectangluarize (note that this creates "fake" town ids that will be dropped when we merge to nhgis data)*
fillin yr_alloc placefips statefips
replace n_proj = 0 if _fillin == 1
replace n_units = 0 if _fillin == 1
replace li_units = 0 if _fillin == 1
replace n_0br = 0 if _fillin == 1
replace n_1br = 0 if _fillin == 1
replace n_2br = 0 if _fillin == 1
replace n_3br = 0 if _fillin == 1
replace n_4br = 0 if _fillin == 1
drop _fillin
rename yr_alloc year

foreach v of varlist * {
	local lab =  "`: var label `v''"
	local lab = "`lab' values from HUD LIHTC Data"
	di "`lab'"
	label var `v' "`lab'"	
}

save "lihtc_placefips_by_year_allocated_formerge.dta", replace


